--(email-report app)
--version 0.8
--pmhesse 2010 only tested on snow leopard
--based on version 0.7 from j.hobbs 2005

-- todo:
-- isolate strange subject line truncation issue
-- add progress bar

--set the variables & constants
set this_month to (do shell script "date \"+%m\"")
set this_day to (do shell script "date \"+%d\"")
set this_year to (do shell script "date \"+%y\"")
set contact_cell_color to "white" as string
set cell_color to "black" as string
set font_color to "white" as string
set font_name to "\\\"Lucida Grande\\\"" as string
set border_width to 0
set header_file to "header.html" as string
set table_file to "table.html" as string
set sorted_table_file to "sortedtable.html" as string
set temp_location to "/tmp/" as string
set output_location to "~/Desktop/"
set output_file to "index.html"
set debug_file to "debug.txt"
set app_handler to "/Applications/TextEdit.app" as string
set debug_app_handler to "/Applications/TextEdit.app" as string
set mail_location to "~/Library/Mail/"

--remove any old files
try
	do shell script "rm " & temp_location & header_file
	do shell script "rm " & temp_location & table_file
	do shell script "rm " & temp_location & sorted_table_file
	
end try

--make the new temp files
do shell script "touch " & temp_location & header_file
do shell script "touch " & temp_location & table_file

--debug code
--try
--	do shell script "rm " & temp_location & debug_file
--	do shell script "touch " & temp_location & debug_file
--end try

--start the html
do shell script "echo \"<html><head><title>" & output_file & "</title>\" | cat >> " & temp_location & header_file

--start the table
do shell script "echo \"<TABLE border=\"" & border_width & "\" width=\"1000\"><FONT FACE=\"" & font_name & "\">\" | cat >> " & temp_location & header_file

do shell script "echo \"<THEAD><TR><TH BGCOLOR = \"" & cell_color & "\"><FONT COLOR=\"" & font_color & "\"><FONT FACE=\"" & font_name & "\">contact<TH BGCOLOR = \"" & cell_color & "\"><FONT COLOR=\"" & font_color & "\"><FONT FACE=\"" & font_name & "\">tot recd<TH BGCOLOR = \"" & cell_color & "\"><FONT COLOR=\"" & font_color & "\"><FONT FACE=\"" & font_name & "\">tot sent<TH BGCOLOR = \"" & cell_color & "\"><FONT COLOR=\"" & font_color & "\"><FONT FACE=\"" & font_name & "\">last email received<TH BGCOLOR = \"" & cell_color & "\"><FONT COLOR=\"" & font_color & "\"><FONT FACE=\"" & font_name & "\">subject received<TH BGCOLOR = \"" & cell_color & "\"><FONT COLOR=\"" & font_color & "\"><FONT FACE=\"" & font_name & "\">last email sent<TH BGCOLOR = \"" & cell_color & "\"><FONT COLOR=\"" & font_color & "\"><FONT FACE=\"" & font_name & "\">subject sent<TBODY>\" | cat >> " & temp_location & header_file

tell application "Address Book"
	
	set first_user_name to first name of my card as string
	set last_user_name to last name of my card as string
	set user_name to first_user_name & " " & last_user_name
	
	repeat with this_person in every person
		
		set first_name to first name of this_person as string
		set last_name to last name of this_person as string
		set address_book_entry_name to first_name & " " & last_name
		set address_book_sort_name to last_name & " " & first_name
		set organization_name to organization of this_person as string
		
		--debug code
		--try
		--	do shell script "echo " & address_book_entry_name & "," & organization_name & " | cat >> " & temp_location & debug_file
		--end try
		
		if address_book_entry_name is not equal to user_name then
			
			set year_last_email_newest to 0
			set month_last_email_newest to 0
			set day_last_email_newest to 0
			set year_last_email_newest_sent to 0
			set month_last_email_newest_sent to 0
			set day_last_email_newest_sent to 0
			set email_address_last_email_newest to "" as string
			set email_address_last_email_newest_sent to "" as string
			set short_year_last_email_newest_sent to "" as string
			set title_last_email_newest to "" as string
			set title_last_email_newest_sent to "" as string
			set path_last_email_newest to "" as string
			set path_last_email_newest_sent to "" as string
			set bad_friend_bit to 0
			set bad_friend_bit_sent to 0
			set mail_count_received to 0
			set mail_count_sent to 0
			set mail_ratio to 0
			
			if address_book_entry_name is not equal to "missing value missing value" then
				
				if email of this_person exists then
					set email_address_count to count of emails of this_person
					
					set iterate_email_address_count to 1
					
					repeat email_address_count times
						
						set address_book_email to value of email iterate_email_address_count of this_person
						
						--get the path of the last email
						set path_last_email to do shell script "mdfind \"(kMDItemAuthorEmailAddresses == '" & address_book_email & "')\" -onlyin " & mail_location & " | awk -F/ '{print $NF $0}' | sort -n | sed -e 's/[^\\/]*\\(\\/.*\\)/\\1/g' | tail -1" as string
						
						
						--get the number of emails sent
						set mail_count_received_add to (do shell script "mdfind \"(kMDItemAuthorEmailAddresses == '" & address_book_email & "')\" -onlyin " & mail_location & "| wc -l | sed 's/ //g'") as integer
						
						set mail_count_received to mail_count_received + mail_count_received_add
						
						--get the date of the last email received from the person
						set date_last_email to do shell script "mdls -name kMDItemContentCreationDate " & quoted form of path_last_email & " | tail -1" as string
						
						--get the message title of the last email
						set title_last_email to do shell script "mdls -name kMDItemSubject -raw " & quoted form of path_last_email & " | tail -1" as string
						
						--debug code
						--						try
						--							do shell script "echo " & address_book_email & "," & "  | cat >> " & temp_location & debug_file
						--						end try
						--						
						--						try
						--							do shell script "echo " & path_last_email & "," & date_last_email & "," & quoted form of title_last_email & "," & "  | cat >> " & temp_location & debug_file
						--						end try
						
						try
							if date_last_email is not equal to "" then
								set date_last_email to characters 30 thru -1 of date_last_email as string
								set date_last_email to characters 1 thru 19 of date_last_email as string
								
								set year_last_email to characters 1 thru 4 of date_last_email as string
								set short_year_last_email to characters 3 thru 4 of date_last_email as string
								set month_last_email to characters 6 thru 7 of date_last_email as string
								set day_last_email to characters 9 thru 10 of date_last_email as string
								
								if year_last_email is greater than year_last_email_newest then
									set year_last_email_newest to year_last_email
									set short_year_last_email_newest to short_year_last_email
									if month_last_email is greater than month_last_email_newest then
										set month_last_email_newest to month_last_email
										if day_last_email is greater than day_last_email_newest then
											set day_last_email_newest to day_last_email
											
											set title_last_email_newest to title_last_email as string
											set path_last_email_newest to path_last_email as string
											set email_address_last_email_newest to address_book_email as string
											
											--debug code
											--try
											--	do shell script "echo " & path_last_email_newest & "," & date_last_email & "," & quoted form of title_last_email_newest & "," & "  | cat >> " & temp_location & debug_file
											--end try
											
										end if
									end if
								end if
							end if
						end try
						
						--write the table line of the received mail
						
						set word_month_last_email to "undefined" as string
						set word_month_last_email_newest to "undefined" as string
						
						if month_last_email_newest is equal to "01" then set word_month_last_email_newest to "January"
						if month_last_email_newest is equal to "02" then set word_month_last_email_newest to "Febuary"
						if month_last_email_newest is equal to "03" then set word_month_last_email_newest to "March"
						if month_last_email_newest is equal to "04" then set word_month_last_email_newest to "April"
						if month_last_email_newest is equal to "05" then set word_month_last_email_newest to "May"
						if month_last_email_newest is equal to "06" then set word_month_last_email_newest to "June"
						if month_last_email_newest is equal to "07" then set word_month_last_email_newest to "July"
						if month_last_email_newest is equal to "08" then set word_month_last_email_newest to "August"
						if month_last_email_newest is equal to "09" then set word_month_last_email_newest to "September"
						if month_last_email_newest is equal to "10" then set word_month_last_email_newest to "October"
						if month_last_email_newest is equal to "11" then set word_month_last_email_newest to "November"
						if month_last_email_newest is equal to "12" then set word_month_last_email_newest to "December"
						
						set human_readable_date_last_email_newest to word_month_last_email_newest & " " & day_last_email_newest & ", " & year_last_email_newest as string
						
						--try
						--	set title_last_email_newest to characters 17 thru -2 of title_last_email_newest as string
						--end try
						
						if title_last_email_newest is equal to "" then
							set title_last_email_newest to "No email from " & address_book_entry_name & " has been found." as string
							set bad_friend_bit to 1
						else
							set bad_friend_bit to 0
						end if
						
						--color code
						set cell_color to "black" as string
						set cell_color_2 to "black" as string
						set font_color to "white" as string
						
						if short_year_last_email_newest_sent is equal to this_year then
							set cell_color to "darkred" as string
							set cell_color_2 to "lightpink" as string
							set font_color to "white" as string
							
							if month_last_email_newest is equal to this_month then
								set cell_color to "green" as string
								set cell_color_2 to "white" as string
								set font_color to "white" as string
								
								if day_last_email_newest is equal to this_day then
									set cell_color to "chartreuse" as string
									set cell_color_2 to "white" as string
									set font_color to "black" as string
									
									
									
								end if
							end if
						end if
						
						if bad_friend_bit is 1 then
							set cell_color to "lightgrey" as string
							set cell_color_2 to "lightgrey" as string
							set font_color to "black" as string
							set human_readable_date_last_email_newest to "N/A"
							set email_address_last_email_newest to address_book_email
						end if
						
						--and now get the last thing they sent you
						
						--get the path of the last email
						set path_last_email_sent to do shell script "mdfind \"(kMDItemRecipientEmailAddresses == '" & address_book_email & "')\" -onlyin " & mail_location & " | awk -F/ '{print $NF $0}' | sort -n | sed -e 's/[^\\/]*\\(\\/.*\\)/\\1/g' | tail -1" as string
						
						--get the number of emails sent
						set mail_count_sent_add to (do shell script "mdfind \"(kMDItemRecipientEmailAddresses == '" & address_book_email & "')\" -onlyin " & mail_location & "| wc -l | sed 's/ //g'") as integer
						
						set mail_count_sent to mail_count_sent + mail_count_sent_add
						
						--get the date of the last email sent to the person
						set date_last_email_sent to do shell script "mdls -name kMDItemContentCreationDate " & quoted form of path_last_email_sent & " | tail -1" as string
						
						--get the message title of the last email
						set title_last_email_sent to do shell script "mdls -name kMDItemSubject -raw " & quoted form of path_last_email_sent & " | tail -1" as string
						set short_year_last_email_newest to "2000" as string
						
						try
							if date_last_email_sent is not equal to "" then
								
								set date_last_email_sent to characters 30 thru -1 of date_last_email_sent as string
								set date_last_email_sent to characters 1 thru 19 of date_last_email_sent as string
								
								set year_last_email_sent to characters 1 thru 4 of date_last_email_sent as string
								set short_year_last_email_sent to characters 3 thru 4 of date_last_email_sent as string
								set month_last_email_sent to characters 6 thru 7 of date_last_email_sent as string
								set day_last_email_sent to characters 9 thru 10 of date_last_email_sent as string
								
								if year_last_email_sent is greater than year_last_email_newest_sent then
									set year_last_email_newest_sent to year_last_email_sent
									set short_year_last_email_newest_sent to short_year_last_email_sent
									if month_last_email_sent is greater than month_last_email_newest_sent then
										set month_last_email_newest_sent to month_last_email_sent
										if day_last_email_sent is greater than day_last_email_newest_sent then
											set day_last_email_newest_sent to day_last_email_sent
											
											set title_last_email_newest_sent to title_last_email_sent as string
											set path_last_email_newest_sent to path_last_email_sent as string
											
										end if
									end if
								end if
							end if
						end try
						
						set iterate_email_address_count to iterate_email_address_count + 1
						
					end repeat
					
					--write the table line of the received mail
					
					set word_month_last_email_sent to "undefined" as string
					if month_last_email_newest_sent is equal to "01" then set word_month_last_email_newest_sent to "January"
					if month_last_email_newest_sent is equal to "02" then set word_month_last_email_newest_sent to "February"
					if month_last_email_newest_sent is equal to "03" then set word_month_last_email_newest_sent to "March"
					if month_last_email_newest_sent is equal to "04" then set word_month_last_email_newest_sent to "April"
					if month_last_email_newest_sent is equal to "05" then set word_month_last_email_newest_sent to "May"
					if month_last_email_newest_sent is equal to "06" then set word_month_last_email_newest_sent to "June"
					if month_last_email_newest_sent is equal to "07" then set word_month_last_email_newest_sent to "July"
					if month_last_email_newest_sent is equal to "08" then set word_month_last_email_newest_sent to "August"
					if month_last_email_newest_sent is equal to "09" then set word_month_last_email_newest_sent to "September"
					if month_last_email_newest_sent is equal to "10" then set word_month_last_email_newest_sent to "October"
					if month_last_email_newest_sent is equal to "11" then set word_month_last_email_newest_sent to "November"
					if month_last_email_newest_sent is equal to "12" then set word_month_last_email_newest_sent to "December"
					
					set human_readable_date_last_email_newest_sent to month_last_email_newest_sent & " " & day_last_email_newest_sent & ", " & year_last_email_newest_sent as string
					
					--try
					--	set title_last_email_newest to characters 16 thru -1 of title_last_email_newest as string
					--	set title_last_email_newest_sent to characters 16 thru -1 of title_last_email_newest_sent as string
					--end try
					
					if title_last_email_newest_sent is equal to "" then
						set title_last_email_newest_sent to "No email to " & address_book_entry_name & " has been found." as string
						set bad_friend_bit_sent to 1
					else
						set bad_friend_bit to 0
						
					end if
					
					--color code
					set cell_color_sent to "black" as string
					set cell_color_2_sent to "black" as string
					set font_color_sent to "white" as string
					
					try
						set mail_ratio to mail_count_received / mail_count_sent
					end try
					if mail_ratio is greater than 1 then
						set contact_cell_color to "lightpink" as string
					else if mail_ratio is less than 1 then
						set contact_cell_color to "chartreuse" as string
					end if
					
					if short_year_last_email_newest_sent is equal to this_year then
						set cell_color_sent to "darkred" as string
						set cell_color_2_sent to "lightpink" as string
						set font_color_sent to "white" as string
						
						if month_last_email_newest_sent is equal to this_month then
							set cell_color_sent to "green" as string
							set cell_color_2_sent to "white" as string
							set font_color_sent to "white" as string
							
							if day_last_email_newest_sent is equal to this_day then
								set cell_color_sent to "chartreuse" as string
								set cell_color_2_sent to "white" as string
								set font_color_sent to "black" as string
								
							end if
						end if
					end if
					
					if bad_friend_bit_sent is 1 then
						set cell_color_sent to "lightgrey" as string
						set cell_color_2_sent to "lightgrey" as string
						set font_color_sent to "black" as string
						set human_readable_date_last_email_newest_sent to "N/A"
						set email_address_last_email_newest to address_book_email
					end if
					
					--remove spaces from file URLs
					set fixed_path_last_email_newest to do shell script "echo " & quoted form of path_last_email_newest & " | sed 's/ /%20/g'"
					set fixed_path_last_email_newest_sent to do shell script "echo \"" & quoted form of path_last_email_newest_sent & "\" | sed 's/ /%20/g'"
					set fixed_path_last_email_newest to do shell script "echo " & quoted form of fixed_path_last_email_newest & " | sed 's/Sent Messages/Sent%20Messages/g'"
					set fixed_path_last_email_newest_sent to do shell script "echo " & quoted form of fixed_path_last_email_newest_sent & " | sed 's/Sent Messages/Sent%20Messages/g'"
					
					--remove apostrophes in subject lines
					set fixed_title_last_email_newest to do shell script "echo " & quoted form of title_last_email_newest & " | sed \"s/\\'//g\""
					set fixed_title_last_email_newest_sent to do shell script "echo " & quoted form of title_last_email_newest_sent & " | sed \"s/\\'//g\""
					
					--remove ampersands in subject lines
					set fixed_title_last_email_newest to do shell script "echo " & quoted form of fixed_title_last_email_newest & " | sed \"s/\\&/+/g\""
					set fixed_title_last_email_newest_sent to do shell script "echo " & quoted form of fixed_title_last_email_newest_sent & " | sed \"s/\\&/+/g\""
					
					--generate table code				
					try
						--debug code
						--display dialog "title:" & fixed_title_last_email_newest buttons {"OK"} default button 1
						
						do shell script "echo \"<!--" & address_book_sort_name & "--><TR><TD BGCOLOR = \"" & contact_cell_color & "\"><FONT COLOR = \"black\"><FONT FACE=\"" & font_name & "\"><a href=\"mailto:" & email_address_last_email_newest & "\">" & address_book_entry_name & "</a> </TD><TD BGCOLOR = \"" & contact_cell_color & "\"><FONT COLOR = \"black\"><FONT FACE=\"" & font_name & "\">" & mail_count_received & "</TD><td BGCOLOR = \"" & contact_cell_color & "\"><FONT COLOR = \"black\"><FONT FACE=\"" & font_name & "\">" & mail_count_sent & "</td><TD BGCOLOR = \"" & cell_color & "\"><FONT COLOR=\"" & font_color & "\"><FONT FACE=\"" & font_name & "\">" & human_readable_date_last_email_newest & "<TD BGCOLOR = \"" & cell_color_2 & "\"><FONT FACE=\"" & font_name & "\"><a href=\"file://" & fixed_path_last_email_newest & "\">" & quoted form of fixed_title_last_email_newest & "</a><TD BGCOLOR = \"" & cell_color_sent & "\"><FONT COLOR=\"" & font_color_sent & "\"><FONT FACE=\"" & font_name & "\">" & human_readable_date_last_email_newest_sent & "<TD BGCOLOR = \"" & cell_color_2_sent & "\"><FONT FACE=\"" & font_name & "\"><a href=\"file://" & fixed_path_last_email_newest_sent & "\">" & quoted form of fixed_title_last_email_newest_sent & "</a>\" | cat >> " & temp_location & table_file
					on error
						
						try
							--replace the problematic email subject in the received mail
							
							--set fixed_title_last_email_newest to "error in email subject"
							
							do shell script "echo \"<!--" & address_book_sort_name & "--><TR><TD BGCOLOR = \"" & contact_cell_color & "\"><FONT COLOR = \"black\"><FONT FACE=\"" & font_name & "\"><a href=\"mailto:" & email_address_last_email_newest & "\">" & address_book_entry_name & "</a> </td><td BGCOLOR = \"" & contact_cell_color & "\"><FONT COLOR = \"black\"><FONT FACE=\"" & font_name & "\">" & mail_count_received & "</td><td BGCOLOR = \"" & contact_cell_color & "\"><FONT COLOR = \"black\"><FONT FACE=\"" & font_name & "\">" & mail_count_sent & "</td><TD BGCOLOR = \"" & cell_color & "\"><FONT COLOR=\"" & font_color & "\"><FONT FACE=\"" & font_name & "\">" & human_readable_date_last_email_newest & "<TD BGCOLOR = \"" & cell_color_2 & "\"><FONT FACE=\"" & font_name & "\"><a href=\"file://" & fixed_path_last_email_newest & "\">" & quoted form of fixed_title_last_email_newest & "</a><TD BGCOLOR = \"" & cell_color_sent & "\"><FONT COLOR=\"" & font_color_sent & "\"><FONT FACE=\"" & font_name & "\">" & human_readable_date_last_email_newest_sent & "<TD BGCOLOR = \"" & cell_color_2_sent & "\"><FONT FACE=\"" & font_name & "\"><a href=\"file://" & fixed_path_last_email_newest_sent & "\">" & quoted form of fixed_title_last_email_newest_sent & "</a>\" | cat >> " & temp_location & table_file
							
						on error
							try
								--replace the problematic email subject in the sent mail
								--set fixed_title_last_email_newest_sent to "error in email subject"
								
								do shell script "echo \"<!--" & address_book_sort_name & "--><TR><TD BGCOLOR = \"" & contact_cell_color & "\"><FONT COLOR = \"black\"><FONT FACE=\"" & font_name & "\"><a href=\"mailto:" & email_address_last_email_newest & "\">" & address_book_entry_name & "</a><TD BGCOLOR = \"" & cell_color & "\"><FONT COLOR=\"" & font_color & "\"><FONT FACE=\"" & font_name & "\">" & human_readable_date_last_email_newest & "<TD BGCOLOR = \"" & cell_color_2 & "\"><FONT FACE=\"" & font_name & "\"><a href=\"file://" & fixed_path_last_email_newest & "\">" & quoted form of fixed_title_last_email_newest & "</a><TD BGCOLOR = \"" & cell_color_sent & "\"><FONT COLOR=\"" & font_color_sent & "\"><FONT FACE=\"" & font_name & "\">" & human_readable_date_last_email_newest_sent & "<TD BGCOLOR = \"" & cell_color_2_sent & "\"><FONT FACE=\"" & font_name & "\"><a href=\"file://" & fixed_path_last_email_newest_sent & "\">" & quoted form of fixed_title_last_email_newest_sent & "</a>\" | cat >> " & temp_location & table_file
							end try
						end try
					end try
					
				end if
				
			end if
			
		end if
		
		--debug code
		--try
		--	do shell script "echo " & return & " | cat >> " & temp_location & debug_file
		--end try
		
	end repeat
	
end tell

--alphabetize by email address
do shell script "sort " & temp_location & table_file & " > " & temp_location & sorted_table_file

--remove unsorted report
do shell script "rm " & temp_location & table_file

--cat the header and the table together
do shell script "cat " & temp_location & header_file & " " & temp_location & sorted_table_file & " > " & output_location & output_file
--do shell script "cat " & temp_location & header_file & " " & temp_location & table_file & " > " & output_location & output_file

--open the document
do shell script "open -a " & app_handler & " " & output_location & output_file

--open the debug file
--do shell script "open -a " & debug_app_handler & " " & temp_location & debug_file